# -*- coding: utf-8 -*-
import MySQLdb
import sys
#提升MYSQL警告等级，方便捕捉和处理错误
from warnings import filterwarnings
filterwarnings('error', category = MySQLdb.Warning)

class Zmysql:
    def __init__(self,info,table=""):
        self.Info=info
        self.t=None
        self.conn=None
        self.table=table
    def Connect_db(self):
        if self.table=="":
            print 'table null'
            return
        try:
            conndb=MySQLdb.connect(host=self.Info['host'],
                                 port=self.Info['port'],
                                 user=self.Info['user'],
                                 passwd=self.Info['passwd'],
                                 db=self.Info['db'])
            print 'table： %s'%self.table
            print 'successful connection'
            self.conn=conndb
            self.t=self.conn.cursor()
        except:
            info=sys.exc_info()
            print 'error:',info[0],":",info[1]

    def Insert(self,ins="",val="",sql=None):
            '''
            Insert("test","id,name","'12','laoshi'")
            :param table: 插入的表名
            :param ins: 插入的列名
            :param val: 插入的数据
            :param sql: 可以自己输入SQL语句
            '''
            if (self.table=="" or ins=="" or val=="") and sql==None:
                print 'error'
                return 'error'
            try:
                if sql==None:
                    s="INSERT INTO %s(%s) VALUES(%s)"%(self.table,ins,val)
                    print 'SQL statement :',s
                    self.t.execute("%s"%s)
                else:
                    print 'SQL statement :',sql
                    self.t.execute("%s"%sql)
            except MySQLdb.Warning,w:
                #发生错误时回滚，输出错误信息
                self.conn.rollback()
                print 'error:',w
            except:
                self.conn.rollback()
                info=sys.exc_info()
                print 'error:',info[0],":",info[1]
    
    def Inc_insert(self,key="",ins="",val=""):
            '''
            :param table: 想要修改的表名
            :param key: 主键
            :param ins: 除主键外要插入的列名,格式：",列名" 双引z双引号，逗号不能省略，可为空
            :param val:插入除了主键数值外的其他数值，格式：",'数值'" 逗号，单引号不要忘了，可为空
            :基本功能:插入一个行数据，根据主键最大值自增1。如果输入错误，步骤回滚，不进行写入。
            :模版:Inc_insert(table='test',key='id',ins=",name",val=",'hello'")
            '''
            if self.table==""or key=="":
                print 'error'
                return "error"
            #获取当前主键最大值
            self.t.execute("select max(%s) FROM %s"%(key,self.table))
            a=self.t.fetchone()
            try:
                if a[0]!=None:
                    #主键+1，再合并上想插入的数据
                    ss="'"+str(a[0]+1)+"'"+val
                    #合并成sql语句
                    sql="INSERT INTO %s(%s%s) VALUES(%s)"%(self.table,key,ins,ss)
                    print 'SQL statement :',sql
                    #输入编辑好的SQL语句
                    self.t.execute("%s"%sql)
                else:
                    #当表是空时
                    sql="INSERT INTO %s(%s%s) VALUES('1'%s)"%(self.table,key,ins,val)
                    print 'SQL statement :',sql
                    self.t.execute("%s"%sql)
            except MySQLdb.Warning,w:
                self.conn.rollback()
                print 'error:',w
            except:
                self.conn.rollback()
                info=sys.exc_info()
                print 'error:',info[0],":",info[1]
            return a[0]+1
    
    def Update(self,set="",where=None,sql=None):
            '''
            :param table: 表名
            :param set: 要修改的公式
            :param where: 判断语句
            :param sql: 前面可以自己输入SQL语句
            '''
            if (self.table=="" or set=="") and sql==None:
                print 'error'
                return 'error'
            try:
                if sql==None:
                    if where!=None:
                        s="UPDATE %s SET %s WHERE %s"%(self.table,set,where)
                        print 'SQL statement :',s
                        self.t.execute("%s"%s)
                    else:
                        s="UPDATE %s SET %s"%(self.table,set)
                        print 'SQL statement :',s
                        self.t.execute("%s"%s)
                else:
                    print 'SQL statement :',sql
                    self.t.execute("%s"%sql)
            except MySQLdb.Warning,w:
                self.conn.rollback()
                print 'error:',w
            except:
                self.conn.rollback()
                info=sys.exc_info()
                print 'error:',info[0],":",info[1]
    
    def Select(self,sel="",where=None,r=0,sql=None):
            '''
            :param table: 表名
            :param sel: 想要搜索的值
            :param where: 判断条件
            :param sql: 可以自己写入SQL语句
            :return:返回与r相应个数的数据，r=-1返回找到的全部数据
            '''
            if (self.table=="" or sel=="") and sql==None:
                print 'error'
                return 'error'
            try:
                if sql==None:
                    if where!=None:
                        s="SELECT %s FROM %s WHERE %s"%(sel,self.table,where)
                        print 'SQL statement :',s
                        self.t.execute("%s"%s)
                    else:
                        s="SELECT %s FROM %s"%(sel,self.table)
                        print 'SQL statement :',s
                        self.t.execute("%s"%s)
                else:
                    print 'SQL statement :',sql
                    self.t.execute("%s"%sql)
                if r <= 0:
                    a=self.t.fetchall()
                else:
                    a=self.t.fetchmany(r)
                print a
                return a    
            except MySQLdb.Warning,w:
                self.conn.rollback()
                print 'error:',w
            except:
                self.conn.rollback()
                info=sys.exc_info()
                print 'error:',info[0],":",info[1]
    
    def Delete(self,where="",sql=None):
            if (self.table=="" or where=="") and sql==None:
                print 'error'
                return 'error'
            try:
                if sql==None:
                    s="DELETE FROM %s WHERE %s"%(self.table,where)
                    print 'SQL statement :',s
                    self.t.execute("%s"%s)
                else:
                    print 'SQL statement :',sql
                    self.t.execute("%s"%sql)
            except MySQLdb.Warning,w:
                self.conn.rollback()
                print 'error:',w
            except:
                self.conn.rollback()
                info=sys.exc_info()
                print 'error:',info[0],":",info[1]
    
    def Print_table(self,where=None):
            if self.table=="":
                print 'error'
                return 'error'
            try:
                self.t.execute("SHOW COLUMNS FROM %s"%self.table)
                a=self.t.fetchall()
                b=''
                if where==None:
                    sql="select * FROM %s"%self.table
                    print 'SQL statement :',sql
                else:
                    sql="select * FROM %s WHERE %s"%(self.table,where)
                    print 'SQL statement :',sql
                for i in range(len(a)):
                    b=b+'%s'%''.join(a[i][0]).ljust(10)
                print '---------+'*len(a)
                print b
                print '---------+'*len(a)
                self.t.execute(sql)
                s=self.t.fetchall()
                if s==():
                    print 'no value'
                    return
                for p in range(len(s)):
                    j=''
                    q=list(s[p])
                    for o in range(len(q)):
                        j=j+str(q[o]).ljust(10)
                    print j
            except:
                info=sys.exc_info()
                print 'error:',info[0],":",info[1]
    
    def Close_db(self):
            try:
                #关闭数据库游标
                self.t.close()
                #提交操作
                self.conn.commit()
                #关闭数据库
                self.conn.close()
            except:
                info=sys.exc_info()
                print 'error:',info[0],":",info[1]

if __name__=="__main__":
    Login_info={}
    Login_info['host']="127.0.0.1"
    Login_info['port']=3307
    Login_info['user']="root"
    Login_info['passwd']="usbw"
    Login_info['db']="test2"

    print Login_info

    db=Zmysql(Login_info,table="student")
    db.Connect_db()
    db.Print_table()
    db.Close_db()
